﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using Entities;
using System.Data.SqlClient;

namespace DAL
{
    public class RouteDetailDAL : BaseObject
    {
        #region Constructor

        ///<summary>
        ///Initializes a new instance of the <see cref="UserDal"/> 
        ///class with the specified <see cref="BaseDAL"/>.
        ///</summary>
        public RouteDetailDAL()
            : base()
        {

        }

        #endregion

        #region Public Methods
        ///<summary>
        ///Create Obj from ireader.
        ///</summary>

        //Chung
        public List<RouteDetail> GetList_RouteDetailByRouteID(int routeID)
        {
            using (SqlConnection conn = CreateConnection())
            {
                SqlCommand cmd = new SqlCommand(this.usp_SelecttbRouteDetailsDynamic1, conn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add(new SqlParameter("@RouteID", routeID));

                conn.Open();
                List<RouteDetail> lst = new List<RouteDetail>();
                using (IDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                {
                    while (reader.Read())
                    {
                        lst.Add(GetObjectFromReader(reader));
                    }
                }
                conn.Close();
                return lst;
            }
        }
        private RouteDetail GetObjectFromReader(IDataReader reader)
        {
            RouteDetail temp = new RouteDetail();
            temp.RouteID = (reader["RouteID"] is DBNull) ? int.MinValue : (System.Int32)reader["RouteID"];
            temp.StationID = (reader["StationID"] is DBNull) ? int.MinValue : (System.Int32)reader["StationID"];
            temp.StationNumber = (reader["StationNumber"] is DBNull) ? int.MinValue : (System.Int32)reader["StationNumber"];
            temp.StationName = (reader["StastionName"] is DBNull) ? String.Empty : (System.String)reader["StastionName"];
            return temp;
        }

        ///<summary>
        ///Get List RouteDetail and store in a list 
        ///</summary>
        public List<RouteDetail> GetList_RouteDetail(string whereSql, string orderBy)
        {
            using (SqlConnection conn = CreateConnection())
            {
                SqlCommand cmd = new SqlCommand(usp_SelecttbRouteDetailsDynamic, conn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add(new SqlParameter("@WhereCondition", whereSql));
                cmd.Parameters.Add(new SqlParameter("@OrderByExpression", orderBy));
                conn.Open();
                List<RouteDetail> lst = new List<RouteDetail>();
                using (IDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                {
                    while (reader.Read())
                    {
                        lst.Add(GetObjectFromReader(reader));
                    }
                }
                conn.Close();
                return lst;
            }
        }

        ///<summary>
        ///Get All RouteDetail and store in a list 
        ///</summary>
        public List<RouteDetail> GetAll_RouteDetail()
        {
            using (SqlConnection conn = CreateConnection())
            {
                SqlCommand cmd = new SqlCommand(usp_SelecttbRouteDetailsAll, conn);
                cmd.CommandType = CommandType.StoredProcedure;
                conn.Open();
                List<RouteDetail> lst = new List<RouteDetail>();
                using (IDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                {
                    while (reader.Read())
                    {
                        lst.Add(GetObjectFromReader(reader));
                    }
                }
                conn.Close();
                return lst;
            }
        }

        ///<summary>
        ///Get List RouteDetail by ID
        ///</summary>
        public RouteDetail Get_RouteDetail_By_ID(int RouteID, int StationID)
        {
            using (SqlConnection conn = CreateConnection())
            {
                SqlCommand cmd = new SqlCommand(usp_SelecttbRouteDetailsByID, conn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add(new SqlParameter("@RouteID", RouteID));
                cmd.Parameters.Add(new SqlParameter("@StationID", StationID));
                RouteDetail temp = new RouteDetail();
                using (IDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                {
                    if (reader.Read())
                    {
                        temp = GetObjectFromReader(reader);
                    }
                }
                conn.Close();
                return temp;
            }
        }

        ///<summary>
        ///Using for paging data 
        ///</summary>
        /// <param name="pageIndex">Number of pages</param>
        /// <param name="PageSize">Number of a rows per page</param>
        /// <param name="conditions">Where  fields</param>
        /// <param name="groupBy">Group by fields</param>
        public DataTable Get_RouteDetailAsPaging(string sWhere, string sOrderBy, int pageIndex, int pageSize)
        {
            using (SqlConnection conn = CreateConnection())
            {
                SqlCommand cmd = new SqlCommand(usp_SelecttbRouteDetailsPaged, conn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add(new SqlParameter("@Where", sWhere));
                cmd.Parameters.Add(new SqlParameter("@OrderBy", sOrderBy));
                cmd.Parameters.Add(new SqlParameter("@PageIndex", pageIndex));
                cmd.Parameters.Add(new SqlParameter("@PageSize", pageSize));
                SqlDataAdapter dataAdapter = new SqlDataAdapter(cmd);
                try
                {
                    conn.Open();
                    DataTable tbl = new DataTable();
                    dataAdapter.Fill(tbl);
                    conn.Close();
                    return tbl;
                }
                catch
                {
                    return null;
                }
                finally
                {
                    if (conn.State == ConnectionState.Open)
                        conn.Close();
                }
            }
        }

        ///<summary>
        ///Insert new RouteDetail
        ///</summary>
        public void Insert_RouteDetail(RouteDetail temp)
        {
            using (SqlConnection conn = CreateConnection())
            {
                SqlCommand cmd = new SqlCommand(usp_InserttbRouteDetail, conn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add(new SqlParameter("@RouteID", temp.RouteID));
                cmd.Parameters.Add(new SqlParameter("@StationID", temp.StationID));
                cmd.Parameters.Add(new SqlParameter("@StationNumber", temp.StationNumber));
                try
                {
                    conn.Open();
                    cmd.ExecuteNonQuery();
                    conn.Close();
                }
                catch (SqlException ex)
                {
                    throw ex;
                }
                finally
                {
                    if (conn.State == ConnectionState.Open)
                        conn.Close();
                }
            }
        }

        ///<summary>
        ///Update a RouteDetail
        ///</summary>
        public void Update_RouteDetail(RouteDetail temp)
        {
            using (SqlConnection conn = CreateConnection())
            {
                SqlCommand cmd = new SqlCommand(usp_UpdatetbRouteDetail, conn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add(new SqlParameter("@RouteID", temp.RouteID));
                cmd.Parameters.Add(new SqlParameter("@StationID", temp.StationID));
                cmd.Parameters.Add(new SqlParameter("@StationNumber", temp.StationNumber));
                try
                {
                    conn.Open();
                    cmd.ExecuteNonQuery();
                    conn.Close();
                }
                catch (SqlException ex)
                {
                    throw ex;
                }
                finally
                {
                    if (conn.State == ConnectionState.Open)
                        conn.Close();
                }
            }
        }

        public void Delete_RouteDetail(RouteDetail temp)
        {
            using (SqlConnection conn = CreateConnection())
            {
                SqlCommand cmd = new SqlCommand(usp_DeletetbRouteDetail, conn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add(new SqlParameter("@RouteID", temp.RouteID));
                cmd.Parameters.Add(new SqlParameter("@StationID", temp.StationID));
                try
                {
                    conn.Open();
                    cmd.ExecuteNonQuery();
                    conn.Close();
                }
                catch (SqlException ex)
                {
                    throw ex;
                }
                finally
                {
                    if (conn.State == ConnectionState.Open)
                        conn.Close();
                }
            }
        }

        public DataTable GetList_Station_By_RouteID(int RouteID)
        {
            using (SqlConnection conn = CreateConnection())
            {
                String str = @"SELECT tbRouteDetail.RouteID, tbRouteDetail.StationID, tbStation.StastionName, tbRouteDetail.StationNumber
                                FROM tbRouteDetail INNER JOIN tbStation ON tbRouteDetail.StationID = tbStation.StationID
                                WHERE (tbRouteDetail.RouteID = " + RouteID.ToString() + @")ORDER BY tbRouteDetail.StationNumber";
                SqlCommand cmd = new SqlCommand(str, conn);
                cmd.CommandType = CommandType.Text;
                SqlDataAdapter dataAdapter = new SqlDataAdapter(cmd);
                try
                {
                    conn.Open();
                    DataTable tbl = new DataTable();
                    dataAdapter.Fill(tbl);
                    conn.Close();
                    return tbl;
                }
                catch
                {
                    return null;
                }
                finally
                {
                    if (conn.State == ConnectionState.Open)
                        conn.Close();
                }
            }
        }

        public bool AddStation_To_RouteDetail(int RouteID, int StationID, int StationNumber)
        {
            using (SqlConnection conn = CreateConnection())
            {
                SqlCommand cmd = new SqlCommand("AddStationToRouteDetail", conn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add(new SqlParameter("@RouteID", RouteID));
                cmd.Parameters.Add(new SqlParameter("@StationID", StationID));
                cmd.Parameters.Add(new SqlParameter("@StationNumber", StationNumber));
                try
                {
                    conn.Open();
                    DataTable tbl = new DataTable();
                    cmd.ExecuteNonQuery();
                    conn.Close();
                    return true;
                }
                catch
                {
                    return false;
                }
                finally
                {
                    if (conn.State == ConnectionState.Open)
                        conn.Close();
                }
            }
        }

        public bool DeleteStation_From_RouteDetail(int RouteID, int StationID)
        {
            using (SqlConnection conn = CreateConnection())
            {
                SqlCommand cmd = new SqlCommand("DeleteStationFromRouteDetail", conn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add(new SqlParameter("@RouteID", RouteID));
                cmd.Parameters.Add(new SqlParameter("@StationID", StationID));
                try
                {
                    conn.Open();
                    DataTable tbl = new DataTable();
                    cmd.ExecuteNonQuery();
                    conn.Close();
                    return true;
                }
                catch
                {
                    return false;
                }
                finally
                {
                    if (conn.State == ConnectionState.Open)
                        conn.Close();
                }
            }
        }

        public bool UpStation_In_RouteDetail(int RouteID, int StationID)
        {
            using (SqlConnection conn = CreateConnection())
            {
                SqlCommand cmd = new SqlCommand("UpStationInRouteDetail", conn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add(new SqlParameter("@RouteID", RouteID));
                cmd.Parameters.Add(new SqlParameter("@StationID", StationID));
                try
                {
                    conn.Open();
                    DataTable tbl = new DataTable();
                    cmd.ExecuteNonQuery();
                    conn.Close();
                    return true;
                }
                catch
                {
                    return false;
                }
                finally
                {
                    if (conn.State == ConnectionState.Open)
                        conn.Close();
                }
            }
        }

        public bool DownStation_In_RouteDetail(int RouteID, int StationID)
        {
            using (SqlConnection conn = CreateConnection())
            {
                SqlCommand cmd = new SqlCommand("DownStationInRouteDetail", conn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add(new SqlParameter("@RouteID", RouteID));
                cmd.Parameters.Add(new SqlParameter("@StationID", StationID));
                try
                {
                    conn.Open();
                    DataTable tbl = new DataTable();
                    cmd.ExecuteNonQuery();
                    conn.Close();
                    return true;
                }
                catch
                {
                    return false;
                }
                finally
                {
                    if (conn.State == ConnectionState.Open)
                        conn.Close();
                }
            }
        }
        #endregion
    }
}
